package com.zzmcc.jms.controller;


import com.jfinal.ext.kit.PoiKit.Pair;

import com.jfinal.ext.render.excel.JxlsRender;

import com.jfinal.kit.PathKit;

import com.jfinal.plugin.activerecord.Db;

import com.jfinal.plugin.activerecord.Record;
import com.zzmcc.base.BaseController;

import util.ChristStringUtil;

import java.util.*;

public class ExportController extends BaseController {

	public void export15(){
		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		String name = getPara("tower.name");

		String addr_code = getPara("tower.addr_code");
		String entry = getPara("tower.entry");
		String owner = getPara("tower.owner");
		String entryer = getPara("tower.entryer");
		String beginDate = getPara("beginDate");
		String endDate = getPara("endDate");
		String home_county = getPara("tower.home_county");
		String image = getPara("tower.image");

		//高级搜索
		String addr_codes =getPara("addr_codes");
		if(ChristStringUtil.isNotEmpty(addr_codes)){
			String[] coderArr = ChristStringUtil.toArray(addr_codes, ",");
			int i=0;
			whee.append(" and addr_code in (\"");
			for (String code : coderArr) {
				i+=1;
				whee.append(code);
				if(i < coderArr.length)
					whee.append("\",\"");

			}
			whee.append("\")");
			System.out.print(whee);

		}

		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and name like ?");
			param.add("%" + name + "%");
		}
		if(ChristStringUtil.isNotEmpty(addr_code)){
			whee.append(" and addr_code like ?");
			param.add("%" + addr_code);
		}
		if(ChristStringUtil.isNotEmpty(entryer)){
			whee.append(" and entryer like ?");
			param.add("%" + entryer + "%");
		}
		if(ChristStringUtil.isNotEmpty(entry)){
			whee.append(" and entry = ?");
			param.add(entry);
		}
		if(ChristStringUtil.isNotEmpty(owner)){
			whee.append(" and owner = ?");
			param.add(owner);
		}
		if(ChristStringUtil.isNotEmpty(home_county)){
			whee.append(" and home_county = ?");
			param.add(home_county);
		}
		if(ChristStringUtil.isNotEmpty(beginDate)){
			whee.append(" and TO_DAYS(entryDate) >= TO_DAYS(?)");
			param.add( beginDate );
		}
		if(ChristStringUtil.isNotEmpty(endDate)){
			whee.append(" and TO_DAYS(entryDate) <= TO_DAYS(?)");
			param.add(endDate);
		}
		if(ChristStringUtil.isNotEmpty(image)){
			whee.append(" and image = ?");
			param.add(image);
		}

		//1.站点信息
		String sql = "select b.* from jms_tower a inner join Tower_info_1 b on a.addr_code=b.c where 1=1 "+ whee.toString();
		List<Record> info1s =Db.find(sql,param.toArray());

		//2.机房内容
		sql = "select b.* from Tower_info_2 b inner join jms_tower a on b.c=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info2s =Db.find(sql,param.toArray());

		//3.机房检查信息
		sql = "select b.* from Tower_info_3 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info3s =Db.find(sql,param.toArray());

		//4.铁塔基础资料
		sql = "select b.* from Tower_info_4 b inner join jms_tower a on b.c=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info4s =Db.find(sql,param.toArray());

		//5.楼面塔抱杆检查信息
		sql = "select b.* from Tower_info_5 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info5s =Db.find(sql,param.toArray());

		//6.铁塔检查信息
		sql = "select b.* from Tower_info_6 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info6s =Db.find(sql,param.toArray());

		//7.蓄电池
		sql = "select b.* from Tower_info_7 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info7s =Db.find(sql,param.toArray());

		//8.开关电源
		sql = "select b.* from Tower_info_8 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info8s =Db.find(sql,param.toArray());

		//9.外市电引入
		sql = "select b.* from Tower_info_9 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info9s =Db.find(sql,param.toArray());

		//10.交流配电箱（配电屏）-低压配电设备
		sql = "select b.* from Tower_info_10 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info10s =Db.find(sql,param.toArray());

		//11.直流配电设备
		sql = "select b.* from Tower_info_11 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info11s =Db.find(sql,param.toArray());

		//12.空调
		sql = "select b.* from Tower_info_12 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info12s =Db.find(sql,param.toArray());

		//13.油机
		sql = "select b.* from Tower_info_13 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info13s =Db.find(sql,param.toArray());

		//14.变压器或调压器
		sql = "select b.* from Tower_info_14 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info14s =Db.find(sql,param.toArray());

		//15.其他设备
		sql = "select b.* from Tower_info_15 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info15s =Db.find(sql,param.toArray());

		Map<String, Object> beans = new HashMap<String, Object>();
		/*beans.put("info1s", info1s);
		beans.put("info2s", info2s);
		beans.put("info3s", info3s);
		beans.put("info4s", info4s);
		beans.put("info5s", info5s);
		beans.put("info6s", info6s);
		beans.put("info7s", info7s);
		beans.put("info8s", info8s);
		beans.put("info9s", info9s);
		beans.put("info10s", info10s);
		beans.put("info11s", info11s);
		beans.put("info12s", info12s);
		beans.put("info13s", info13s);*/
		beans.put("info14s", info14s);
		beans.put("info15s", info15s);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_15.xls";

		String filename = "tower_15.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));


	}

	public void export11(){

		//导出条件
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();
		String name = getPara("tower.name");

		String addr_code = getPara("tower.addr_code");
		String entry = getPara("tower.entry");
		String owner = getPara("tower.owner");
		String entryer = getPara("tower.entryer");
		String beginDate = getPara("beginDate");
		String endDate = getPara("endDate");
		String home_county = getPara("tower.home_county");
		String image = getPara("tower.image");

		//高级搜索
		String addr_codes =getPara("addr_codes");
		if(ChristStringUtil.isNotEmpty(addr_codes)){
			String[] coderArr = ChristStringUtil.toArray(addr_codes, ",");
			int i=0;
			whee.append(" and addr_code in (\"");
			for (String code : coderArr) {
				i+=1;
				whee.append(code);
				if(i < coderArr.length)
					whee.append("\",\"");

			}
			whee.append("\")");
			System.out.print(whee);

		}

		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and name like ?");
			param.add("%" + name + "%");
		}
		if(ChristStringUtil.isNotEmpty(addr_code)){
			whee.append(" and addr_code like ?");
			param.add("%" + addr_code);
		}
		if(ChristStringUtil.isNotEmpty(entryer)){
			whee.append(" and entryer like ?");
			param.add("%" + entryer + "%");
		}
		if(ChristStringUtil.isNotEmpty(entry)){
			whee.append(" and entry = ?");
			param.add(entry);
		}
		if(ChristStringUtil.isNotEmpty(owner)){
			whee.append(" and owner = ?");
			param.add(owner);
		}
		if(ChristStringUtil.isNotEmpty(home_county)){
			whee.append(" and home_county = ?");
			param.add(home_county);
		}
		if(ChristStringUtil.isNotEmpty(beginDate)){
			whee.append(" and TO_DAYS(entryDate) >= TO_DAYS(?)");
			param.add( beginDate );
		}
		if(ChristStringUtil.isNotEmpty(endDate)){
			whee.append(" and TO_DAYS(entryDate) <= TO_DAYS(?)");
			param.add(endDate);
		}
		if(ChristStringUtil.isNotEmpty(image)){
			whee.append(" and image = ?");
			param.add(image);
		}

		/*
		 *	铁塔物理站址编号表
		 *b序号 c物理站址名称	d物理站址编号	e所在地点	 f站址地理位置经度（度分秒）	g站址地理位置纬度（度分秒）	h覆盖场景 i备注
		 */

		String sql = "select b.b as c,b.c as d,a.addr as e,b.m as f,b.n as g,b.o as h " +
				" from jms_tower a inner join Tower_info_1 b on a.addr_code=b.c where 1=1 "+ whee.toString();
		List<Record> info1s =Db.find(sql,param.toArray());

		/*
		*2-1固定资产-自有基站机房
		*
		* b序号	c资产编号或标签号	d资产目录编号	 e物理站址编号 f资产名称	g所在地点
		* h结构形式	i机房建筑面积(平方米)	j启用日期	 k资产责任人	l使用状况 m机房分类
		* n是否为自有局所、自有综合楼、自有营业厅中的一小间	o盘点状态	 p盘点说明	q盘点标签号
		*/

		sql = "select b.id as b,b.d as c,b.c as e,b.b as f,a.addr as g,b.f as h,b.g*b.h as i ,b.r as l,e as m,b.status ,b.memo ,b.label " +
				" from Tower_info_2 b inner join jms_tower a on b.c=a.addr_code where b.e<>'租赁机房' "+ whee.toString();
		List<Record> info2s =Db.find(sql,param.toArray());

		/*
		*2-2固定资产－铁塔
		*
		* b序号	c资产编号或标签号	d资产目录编号	e物理站址编号	f资产名称	 g所在地点
		* h启用日期	i资产责任人	j铁塔形式	 k铁塔类型	l高度(米)	m使用状况 	n维护形式	 o盘点状态	p盘点说明	 q盘点标签号
		*/

		sql = "select b.d as c,b.c as e,a.name as f,a.addr as g,b.e as l,b.g as k,b.status,b.memo ,b.label " +
				" from Tower_info_4 b inner join jms_tower a on b.c=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info3s =Db.find(sql,param.toArray());

		/*
		* 2-3固定资产－电源设备
		* b序号	c资产编号或标签号	d资产目录编号	e物理站址编号	f设备名称	 g所在地点	h规格型号	 i生产厂家	j计量单位	 k数量
		* l启用日期	m使用状况	 n资产责任人	o盘点状态 	p盘点说明	 q盘点标签号
		*
		 */
		sql = "select b.e,b.f,a.addr as g,b.h,b.i,b.k,b.l,b.m ,b.status,b.memo,b.label " +
				"from (select   b,b as e ,'开关电源' as f,f as h ,g as i,e as k,t as l,u as m,status,memo,label from tower_info_8 " +
				"union all " +
				"select  b,b as e ,'蓄电池' as f,f as h ,g as i,h as k,v as l,w as m,status,memo,label from tower_info_7 " +
				"union all " +
				"select  b,b as e ,'交流配电箱' as f,h as h ,i as i,'' as k,k as l,e as m,status,memo,label from tower_info_10 " +
				") b " +
				"inner join jms_tower a on b.b=a.addr_code where 1=1 " + whee.toString();

		List<Record> info4s =Db.find(sql,param.toArray());

		/*
		* 2-4固定资产－空调设备
		* b序号	c资产编号或标签号	d资产目录编号	e物理站址编号	f设备名称	 g所在地点	h规格型号	 i生产厂家	j计量单位	 k数量
		* l启用日期	m使用状况	 n资产责任人	o盘点状态 	p盘点说明	 q盘点标签号
		*
		 */
		sql = "select b.b as e,b.e as f,a.addr as g,b.f as h,b.g as i,b.h as k,b.r as l,b.i as m,b.status,b.memo,b.label " +
				" from Tower_info_12 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info5s =Db.find(sql,param.toArray());

		/*
		* 2-5固定资产-其他设备
		* b序号	c资产编号或标签号	d资产目录编号	e物理站址编号	f设备名称	 g所在地点	h规格型号	 i生产厂家	j计量单位	 k数量
		* l启用日期	m使用状况	 n资产责任人	o盘点状态 	p盘点说明	 q盘点标签号
		*
		*/
		sql = "select b.e,b.f,a.addr as g,b.h,b.i,b.l,b.m ,b.status,b.memo,b.label " +
				"from (select b,b as e,e as f,g as h,h as i,i as l,f as m,status ,memo ,label from tower_info_15 " +
				"union all " +
				"select  b,b as e,'变压器或调压器' as f,h as h,f as i,n as l,g as m, status,memo,label from tower_info_14 ) b " +
				"inner join jms_tower a on b.b=a.addr_code where 1=1 " + whee.toString();


		List<Record> info6s =Db.find(sql,param.toArray());

		/*2-6固定资产-外电引入资产
		*b序号	c资产编号或标签号	d资产目录编号	e物理站址编号	f资产名称	 g所在地点
		* h规格型号	i生产厂家 j敷设方式	k计量单位	 l数量
		* m启用日期	n使用状况	 o资产责任人	p盘点状态	 q盘点说明	r盘点标签号
		*/
		sql = "select  b.b as e,a.addr as g,b.f as h,b.e as i,b.l as j,b.status ,b.memo ,b.label " +
				" from Tower_info_9 b inner join jms_tower a on b.b=a.addr_code where 1=1 "+ whee.toString();
		List<Record> info7s =Db.find(sql,param.toArray());
		List<Pair> title7s = new ArrayList<Pair>();

		/*
		* 5-1租赁基站机房
		*
		* b序号	c物理站址编号	d所在地点	 e建筑面积(平方米)	f出租方全称	g是否合租	 h合租方全称
		* i是否有租赁合同	 j租金支付方式	k租赁启始日期	 l租赁终止日期	m租金形式
		* n租金内涵	o是否有续租权	 p使用状况	q机房分类	 r盘点状态	s盘点说明	 t盘点标签号

		*/

		sql = "select  b.c as c ,a.addr as d,b.g*b.h as e,b.status ,b.memo ,b.label  " +
				" from Tower_info_2 b inner join jms_tower a on b.c=a.addr_code where b.e='租赁机房' "+ whee.toString();
		List<Record> info8s =Db.find(sql,param.toArray());

		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("info1s", info1s);
		beans.put("info2s", info2s);
		beans.put("info3s", info3s);
		beans.put("info4s", info4s);
		beans.put("info5s", info5s);
		beans.put("info6s", info6s);
		beans.put("info7s", info7s);
		beans.put("info8s", info8s);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_11.xls";
		String filename = "tower_11.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));


	}

}
